﻿using EasyERP_Model;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace EasyERP_DAL.Base
{
    /// <summary>
    /// 人员信息管理数据访问类
    /// </summary>
    public class B_User_DAL
    {
        #region 获取人员信息表
        /// <summary>
        /// 获取人员信息表
        /// </summary>
        /// <param name="model">人员信息实体对象</param>
        /// <returns>查询到的表</returns>
        public DataTable Get_B_User(B_User model)
        {
            //sql语句
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat($@"
                SELECT user_cd,
                       user_nm,
                       tel_no,
                       dept_nm,
                       remark
                FROM dbo.b_user
                WHERE user_nm LIKE N'%{model.user_nm}%';
            ");

            return DbHelperSQL.Query(sb.ToString()).Tables[0];
        }
        #endregion

        #region 添加人员信息信息
        /// <summary>
        /// 添加人员信息信息
        /// </summary>
        /// <param name="model">人员信息实体对象</param>
        /// <returns>返回True：成功；返回False：失败</returns>
        public bool Insert_B_User(B_User model)
        {
            //sql语句
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat($@"
                INSERT INTO dbo.b_user
                (
                    user_cd,
                    user_nm,
                    tel_no,
                    dept_nm,
                    remark,
                    ins_user,
                    ins_date,
                    up_user,
                    up_date
                )
                VALUES
                (   N'{model.user_cd}',
                    N'{model.user_nm}',
                    N'{model.tel_no}',
                    N'{model.dept_nm}',
                    N'{model.remark}',
                    N'{model.user_cd1}',
                    GETDATE(),
                    N'{model.user_cd1}',
                    GETDATE()
                );
            ");

            return DbHelperSQL.ExecuteSql(sb.ToString()) > 0;
        }
        #endregion

        #region 删除人员信息信息
        /// <summary>
        /// 删除人员信息信息
        /// </summary>
        /// <param name="model">人员信息实体对象</param>
        /// <returns>返回True：成功；返回False：失败</returns>
        public bool Delete_B_User(B_User model)
        {
            //sql语句
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat($@"
                DELETE dbo.b_user
                WHERE user_cd = N'{model.user_cd}';
            ");

            return DbHelperSQL.ExecuteSql(sb.ToString()) > 0;
        }
        #endregion

        #region 修改人员信息信息
        /// <summary>
        /// 修改人员信息信息
        /// </summary>
        /// <param name="model">人员信息实体对象</param>
        /// <returns>返回True：成功；返回False：失败</returns>
        public bool Update_B_User(B_User model)
        {
            //sql语句
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat($@"
                UPDATE dbo.b_user
                SET user_nm = N'{model.user_nm}',
                    tel_no = N'{model.tel_no}',
                    dept_nm = N'{model.dept_nm}',
                    remark = N'{model.remark}',
                    up_user = N'{model.user_cd1}',
                    up_date = GETDATE()
                WHERE user_cd = N'{model.user_cd}';
             ");

            return DbHelperSQL.ExecuteSql(sb.ToString()) > 0;
        }
        #endregion

        #region 查询数据是否存在
        /// <summary>
        /// 查询数据是否存在
        /// </summary>
        /// <param name="model">人员信息实体对象</param>
        /// <returns>返回True：存在；返回False：不存在</returns>
        public bool Exists(B_User model)
        {
            //sql语句
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat($@"
                SELECT 1
                FROM dbo.b_user
                WHERE user_cd = N'{model.user_cd}';
            ");

            return DbHelperSQL.Exists(sb.ToString());
        }
        #endregion
    }
}
